﻿using QuanLyCongDan.DTO;
using System.Data.Common;
using System.Data;
using System;
using System.Collections.Generic;

namespace DAL
{
    public class DAL_Field
    {
        /// <summary>
        /// Insert this object Field into database.
        /// </summary>
        /// <param name="donViInput">The inserting Field object</param>
        /// <returns>True if insert successfully, false if not</returns>
        public static bool InsertField(DTO_Field fieldInput, string maDonVi)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText =
                "INSERT INTO DonVi_Field " +
                "([MaDonVi], [MaField], [Value]) " +
                "VALUES " +
                "(@MADV, @MAFIELD, @Value)";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@MADV";
            param.Value = string.IsNullOrEmpty(maDonVi) ? string.Empty : maDonVi;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@MAFIELD";
            param.Value = fieldInput.MaField;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@Value";
            param.Value = fieldInput.Value;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            // execute the command
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }

        /// <summary>
        /// Retrieves all Field of a DonVi in database.
        /// </summary>
        /// <returns>List of all Field of this DonVi.</returns>
        public static List<DTO_Field> GetListFieldDonVi(string maDonVi)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = 
                "SELECT F.ID, F.FieldName, DVF.Value " + 
                "FROM Field as F, DonVi_Field as DVF " +
                "WHERE F.ID = DVF.MaField AND DVF.MaDonVi = @MA";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@MADV";
            param.Value = string.IsNullOrEmpty(maDonVi) ? string.Empty : maDonVi;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            List<DTO_Field> fieldList = new List<DTO_Field>();
            // execute the command
            DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
            foreach (DataRow dr in dt.Rows)
            {
                DTO_Field curField = new DTO_Field();
                curField.MaField = (int)dr["ID"];
                curField.TenField = (dr["FieldName"] == System.DBNull.Value) ? string.Empty : (string)dr["FieldName"];
                curField.Value = (int)dr["Value"];
                fieldList.Add(curField);
            }
            return fieldList;
        }

        /// <summary>
        /// Retrieves all Field in database.
        /// </summary>
        /// <returns>List of all Field.</returns>
        public static List<DTO_Field> GetListField()
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText =
                "SELECT F.ID, F.FieldName " +
                "FROM Field as F";

            List<DTO_Field> fieldList = new List<DTO_Field>();
            // execute the command
            DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
            foreach (DataRow dr in dt.Rows)
            {
                DTO_Field curField = new DTO_Field();
                curField.MaField = (int)dr["ID"];
                curField.TenField = (dr["FieldName"] == System.DBNull.Value) ? string.Empty : (string)dr["FieldName"];
                
                fieldList.Add(curField);
            }
            return fieldList;
        }
    }
}
